﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using DBUtil;
namespace ZXCompany.Models.Dao
{
    public class ZX_ExpertInfoDao
    {
        private Database db = new Database();
        private List<Entity.ZX_ExpertInfo> GetListFromDT(DataTable dt)
        {   
            List<Entity.ZX_ExpertInfo> lexpert = new List<Entity.ZX_ExpertInfo>();
            foreach (DataRow dr in dt.Rows)
            {
                Entity.ZX_ExpertInfo expert = new Entity.ZX_ExpertInfo();
                expert.ID = Convert.ToInt32(dr["ID"]);
                expert.sort = Convert.ToInt32(dr["sort"]);
                expert.updateTime = dr["updateTime"].ToString();
                expert.expertDetail = dr["expertDetail"].ToString();
                expert.expertName = dr["expertName"].ToString();
                expert.expertPostion = dr["expertPostion"].ToString();
                expert.imageUrl = dr["imageUrl"].ToString();
                expert.typeID = Convert.ToInt32(dr["typeID"]);
                expert.typeName = dr["typeName"].ToString();
                expert.addUserID = Convert.ToInt32(dr["addUserID"]);
                expert.addTime = dr["addTime"].ToString();
                lexpert.Add(expert);
            }
            return lexpert;
        }
        /// <summary>
        /// 根据id获取信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_ExpertInfo> Select( SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                            ,isnull([typeID],0) as [typeID]
                            ,isnull((select top 1 etype from ZX_ExpertType where ZX_ExpertType.id=typeID) ,'') as [typeName]
                            ,isnull([imageUrl],'') as imageUrl
                            ,isnull([expertName],'') as expertName
                            ,isnull([expertPostion],'') as expertPostion
                            ,isnull([expertDetail],'') as expertDetail
                            ,isnull([sort],0) as [sort]
                            ,isnull([addUserID],0) as [addUserID]
                            ,isnull([addTime],'') as [addTime]
                            ,isnull([updateTime],'') as [updateTime]
                        FROM [ZX_ExpertInfo]";
                DataTable dt = db.Squery(sql, sconn);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据id获取信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_ExpertInfo> SelectById(int id, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                            ,isnull([typeID],0) as [typeID]
                            ,isnull((select top 1 etype from ZX_ExpertType where ZX_ExpertType.id=typeID) ,'') as [typeName]
                            ,isnull([imageUrl],'') as imageUrl
                            ,isnull([expertName],'') as expertName
                            ,isnull([expertPostion],'') as expertPostion
                            ,isnull([expertDetail],'') as expertDetail
                            ,isnull([sort],0) as [sort]
                            ,isnull([addUserID],0) as [addUserID]
                            ,isnull([addTime],'') as [addTime]
                            ,isnull([updateTime],'') as [updateTime]
                        FROM [ZX_ExpertInfo] where ID=@ID";
                SqlParameter[] spvalues=new SqlParameter[]{
                new SqlParameter("@ID",id)
                };
                DataTable dt = db.Squery(sql, sconn, spvalues);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据id获取信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<Entity.ZX_ExpertInfo> SelectByTypeId(int typeid, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                                      ,isnull([typeID],0) as [typeID]
                            ,isnull((select top 1 etype from ZX_ExpertType where ZX_ExpertType.id=typeID) ,'') as [typeName]
                                      ,isnull([imageUrl],'') as imageUrl
                                      ,isnull([expertName],'') as expertName
                                      ,isnull([expertPostion],'') as expertPostion
                                      ,isnull([expertDetail],'') as expertDetail
                                      ,isnull([sort],0) as [sort]
                                      ,isnull([addUserID],0) as [addUserID]
                                      ,isnull([addTime],'') as [addTime]
                                      ,isnull([updateTime],'') as [updateTime]
                                  FROM [ZX_ExpertInfo] where typeID=@typeID";
                SqlParameter[] spvalues = new SqlParameter[]{
                new SqlParameter("@typeID",typeid)
                };
                DataTable dt = db.Squery(sql, sconn, spvalues);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Delete(int typeid, SqlConnection sconn,SqlTransaction stran)
        {
            try
            {
                string sql = @"delete FROM [ZX_ExpertInfo] where ID=@ID";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@ID",typeid)
                };
                db.Udaquery(sql,sconn,stran,spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Insert(Entity.ZX_ExpertInfo zxei, SqlConnection sconn,SqlTransaction stran)
        {
            try
            {
                string sql = @"insert into ZX_ExpertInfo 
                              (typeID,imageUrl,expertName,expertPostion,expertDetail,sort,addUserID,addTime) values 
                              ( @typeID
                                ,@imageUrl
                                ,@expertName
                                ,@expertPostion
                                ,@expertDetail
                                ,@sort
                                ,@addUserID
                                ,@addTime)";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@typeID",Database.SqlNull(zxei.typeID)),
                    new SqlParameter("@imageUrl",Database.SqlNull(zxei.imageUrl)),
                    new SqlParameter("@expertName",Database.SqlNull(zxei.expertName)),
                    new SqlParameter("@expertPostion",Database.SqlNull(zxei.expertPostion)),
                    new SqlParameter("@expertDetail",Database.SqlNull(zxei.expertDetail)),
                    new SqlParameter("@sort",Database.SqlNull(zxei.sort)),
                    new SqlParameter("@addUserID",Database.SqlNull(zxei.addUserID)),
                    new SqlParameter("@addTime",Database.SqlNull(zxei.addTime))
                };
                db.Udaquery(sql, sconn,stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据id获取信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Update(Entity.ZX_ExpertInfo zxei, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = "update ZX_ExpertInfo set [typeID]=@typeID";
                if (zxei.imageUrl!="")
                {
                    sql += ",[imageUrl]=@imageUrl";
                }
                sql +=@",[expertName]=@expertName
                        ,[expertPostion]=@expertPostion
                        ,[expertDetail]=@expertDetail
                        ,[sort]=@sort
                        ,[addUserID]=@addUserID
                        ,[addTime]=@addTime
                        ,[updateTime]=@updateTime
                    FROM [ZX_ExpertInfo] where [ID]=@ID";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@ID",zxei.ID),
                    new SqlParameter("@typeID",Database.SqlNull(zxei.typeID)),
                    new SqlParameter("@imageUrl",Database.SqlNull(zxei.imageUrl)),
                    new SqlParameter("@expertName",Database.SqlNull(zxei.expertName)),
                    new SqlParameter("@expertPostion",Database.SqlNull(zxei.expertPostion)),
                    new SqlParameter("@expertDetail",Database.SqlNull(zxei.expertDetail)),
                    new SqlParameter("@sort",Database.SqlNull(zxei.sort)),
                    new SqlParameter("@addUserID",Database.SqlNull(zxei.addUserID)),
                    new SqlParameter("@addTime",Database.SqlNull(zxei.addTime)),
                    new SqlParameter("@updateTime",Database.SqlNull(zxei.updateTime))
                };
                db.Udaquery(sql, sconn, stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}